clear all
set matsize 4000
*set type double, permanently

*add file path
gl dat ""
gl do ""
gl text ""
gl texg ""



*****************************
**  co-national co-workers **
***************************** 

use "$dat/ZEMIS_start.dta", clear
drop if mi(cant_res)
keep if (getF==1|getBC==1) // keep even if left the country
keep id country ethn_cd spr_cd cant_res year bur_id industry2 emp emp_start


g bur_id_temp=bur_id 
replace bur_id_temp=. if bur_id==-2
g industry2_temp=industry2
replace industry2_temp=. if (industry2==-2 |industry2==-1) 
 
sum emp_start if emp==0  & emp_start!=-2
sum bur_id if emp==0 // some have bur_id but are not coded as employed, all of them happen before year 2000 so not an issue for our sample
sum bur_id if emp==0 & year<2000
sum industry2_temp if emp==0 // some have industry2 but are not coded as employed, all of them happen before year 2000 so not an issue for our sample
sum industry2_temp if emp==0 & year<2000

*compromise, only add if employment start date is the current year 
g emp_temp=emp
replace emp_temp=1 if emp==0 & year==int(emp_start/100) //  5000 obs, when work permit start
replace bur_id_temp=. if emp_temp==0
replace industry2_temp=. if emp_temp==0 

 
foreach x in conat coeth colan {
g `x'_cow_pre=.
g `x'_emp_pre=.
g `x'_sec_pre=.
g `x'_cant_cow_pre=.
g `x'_cant_emp_pre=.
g `x'_cant_sec_pre=.
}


*  nationality/ethnicity/language of co-workers employed before or same year person gets employed by firm
*  share co-nationals/co-ethn/co-lang employed in canton before or same year a person gets employed
*  share co-nationals employed in same industry before or same year a person gets employed

cap drop  uniq_* 
bys cant_res bur_id_temp id  (year): g uniq_firm=_n==1 if !mi(bur_id_temp) // each individual-firm-employed observation 
bys cant_res id emp_temp (year): g uniq_emp=_n==1 // each employed-individual observation
replace uniq_emp=0 if emp_temp==0
bys cant_res industry2_temp id emp_temp (year): g uniq_indu=_n==1 // each employed-industry-individual observation
replace uniq_indu=0 if emp_temp==0

replace ethn_cd= . if ethn_cd ==-1 | ethn_cd ==998
replace spr_cd= . if spr_cd ==-1 | spr_cd ==998
replace country= . if country ==-1  | country ==998

keep if uniq_firm==1 | uniq_emp==1 |uniq_indu==1 // to speed up

forvalues y=2008/2017 {
foreach x in conat coeth colan  {
if "`x'"=="conat" loc t country 
if "`x'"=="coeth" loc t ethn_cd
if "`x'"=="colan" loc t spr_cd
bys cant_res bur_id_temp (year): egen `x'_cant_cow_`y'=sum(uniq_firm) if !mi(bur_id_temp) & !mi(`t')  & year<=`y'
bys cant_res bur_id_temp `t' (year): egen `x'_cow_`y'=sum(uniq_firm) if !mi(bur_id_temp) & !mi(`t')  & year<=`y'
bys cant_res (year): egen `x'_cant_emp_`y'=sum(uniq_emp) if  !mi(`t')  & year<=`y'
bys cant_res `t' (year): egen `x'_emp_`y'=sum(uniq_emp) if !mi(`t')  & year<=`y'
bys cant_res industry2_temp (year): egen `x'_cant_sec_`y'=sum(uniq_indu) if !mi(`t') & !mi(industry2_temp) & year<=`y'
bys cant_res industry2_temp `t' (year): egen `x'_sec_`y'=sum(uniq_indu) if !mi(`t') & !mi(industry2_temp) & year<=`y'
replace `x'_cow_pre=`x'_cow_`y' if year==`y'  
replace `x'_emp_pre=`x'_emp_`y' if year==`y'
replace `x'_sec_pre=`x'_sec_`y' if year==`y'
drop  `x'_cow_`y' `x'_emp_`y' `x'_sec_`y'
replace `x'_cant_cow_pre=`x'_cant_cow_`y' if year==`y'
replace `x'_cant_emp_pre=`x'_cant_emp_`y' if year==`y' 
replace `x'_cant_sec_pre=`x'_cant_sec_`y' if year==`y'
drop `x'_cant_cow_`y' `x'_cant_emp_`y' `x'_cant_sec_`y'
}
}

drop if year<2008 // to speed up

*create shares
*remove own person from number of co-workers
foreach y in cow emp sec {
foreach x in conat coeth colan {
replace `x'_`y'_pre=`x'_`y'_pre-1 if !mi(`x'_`y'_pre) & `x'_`y'_pre!=0 
g share_`x'_`y'=(`x'_`y'_pre)/(`x'_cant_`y'_pre-1) if !mi(`x'_`y'_pre) & !mi(`x'_cant_`y'_pre)  
}
}


foreach x in conat coeth colan {
foreach y in cow emp sec {
preserve
if "`x'"=="conat" loc t country 
if "`x'"=="coeth" loc t ethn_cd
if "`x'"=="colan" loc t spr_cd
if "`y'"=="cow" {
keep if uniq_firm==1
keep uniq_firm id  year `t' share_`x'_`y' `x'_`y'_pre `x'_cant_`y'_pre
la var uniq_firm "First employer-employee observation" 
la var share_`x'_`y' "Share co-worker, `x'" 
la var `x'_cant_`y'_pre "Total employees, firm (not missing `x' info)"
la var `x'_`y'_pre "Number of `x' co-workers"
}
if "`y'"=="sec" {
keep if uniq_indu==1
keep uniq_indu id   year `t' share_`x'_`y' `x'_cant_`y'_pre
la var uniq_indu "First sector-employment observation" 
la var share_`x'_`y' "Share sector-employees, `x'" 
la var `x'_cant_`y'_pre "Total employees, sector (not missing `x' info)"
}
if "`y'"=="emp" {
keep if uniq_emp==1
keep uniq_emp id  year `t' share_`x'_`y' `x'_cant_`y'_pre
la var uniq_emp "First employment observation" 
la var share_`x'_`y' "Share employed canton, `x'" 
la var `x'_cant_`y'_pre "Total employees, canton (not missing `x' info)"
}
if "`y'"=="cow" save "$dat/workplace_stat_`x'.dta", replace
else merge 1:1 id year using "$dat/workplace_stat_`x'.dta"
cap drop _merge
sleep 2000
save "$dat/workplace_stat_`x'.dta", replace
restore
}
}


*****************************************************************
** Number of F-people per canton and year, based on Zemis data **  
*****************************************************************
*statpop and petra use different restrictions for f-people (only include those who have been in the country for a certain time), code F-people from zemis instead
*when F-people transfer to B-permit they will be included in PETRA or STATPOP

foreach z in all age {
use "$dat/ZEMIS_start.dta", clear
drop if mi(cant_res) | mi(country)
keep if stus_flag==40 // time in Switzerland  on F-permit
if "`z'"=="age" keep if age>=18 & age<=65

keep cant_res year country 

g fmig=0 

drop if  country==-1 // include 998 in fmig
levelsof country, local(clist)
foreach c of local clist {
bys cant_res year: egen c`c'=sum(country==`c') 
cap replace fmig=fmig+c`c' if !mi(c`c') // all immigrants in canton
}

drop if country==998 //  nationless
drop c998 


bys cant_res year: g new= _n==1
keep if new==1
drop new country
ren cant_res cantass
replace year=year+1
ren year fyear

save "$dat/cant_fperm_`z'.dta", replace
}


**********************************************************
**  number of co-ethnicity/language based on ZEMIS data **
**********************************************************

use "$dat/ZEMIS_start.dta", clear
keep if (getF==1|getBC==1)
drop if ygetF<0 & getF==1 // exclude time on N-permit
drop if ygetBC<0 & getBC==1 & getF==0
keep if stus_flag==40 | (stat_end<= 510900 & !mi(stat_end))  // remove if left the country

keep if age>=18 & age<=65
drop if mi(cant_res) 
keep id cant_res ethn_cd spr_cd  year



foreach x in ethn spr {
preserve
drop if (`x'_cd==-1|`x'_cd==998) // can't match these individuals
if "`x'"=="ethn" drop spr_cd
if "`x'"=="spr" drop ethn_cd

levelsof `x'_cd, local(clist)
foreach c of local clist {
bys cant_res year: egen e`c'=sum(`x'_cd==`c') 
}


bys cant_res year: g new= _n==1
keep if new==1

drop `x'_cd new id
ren cant_res cantass
replace year=year+1
ren year fyear
save "$dat/cant_num_`x'.dta", replace
restore
}



*************************************************************************************
**  share co-nationals belonging to ethnic/linguistic group in canton (from ZEMIS) **
*************************************************************************************

use "$dat/ZEMIS_start.dta", clear
keep if (getF==1|getBC==1)
drop if ygetF<0 & getF==1 // exclude time on N-permit
drop if ygetBC<0 & getBC==1 & getF==0
keep if stus_flag==40 | (stat_end<= 510900 & !mi(stat_end))  // remove if left the country

keep if age>=18 & age<=65
drop if mi(cant_res)| mi(country)

g individual=1
 
keep year  ethn_cd  spr_cd country individual cant_res

foreach x in ethn  spr {
foreach y in  1 2 {
preserve
if `y'==1 {
drop if inlist(`x'_cd, 998, -1) // alt definition, include them when counting shares and then drop 
}
drop if mi(`x'_cd)
bys country cant_res year : egen country_sum=sum(individual)
bys country `x'_cd cant_res year: egen group_sum=sum(individual)
g share=group_sum/country_sum
bys country `x'_cd cant_res year: replace share=. if _n!=1
keep country  cant_res year `x'_cd share 
drop if mi(share)
if `y'==2 ren share share_alt
replace year=year+1
ren year fyear
ren cant_res cantass
drop if inlist(`x'_cd, 998, -1)
if `y'==1 save "$dat/file_`x'_share", replace
else merge 1:1 country cantass fyear `x'_cd  using "$dat/file_`x'_share"
cap drop _merge
save "$dat/file_`x'_share", replace
restore
}
}


**************
****  Map  ***
**************


foreach q in all age {
if "`q'"=="all" {
use "$dat/cant_`q'.dta", clear
keep  fyear cantass  c348  c362 c501 c512 c541 c239 c302 c506 c508 c513
keep if fyear==2014 //actual year=2013
}
else merge 1:1 cantass fyear using "$dat/cant_`q'.dta", keepusing(c348  c362 c501 c512 c541 c239 c302 c506 c508 c513) 
cap drop if _merge==2
cap drop _merge

g somalia_`q'=c348
g eritrea_`q'=c362
g afghanistan_`q'=c501
g iraq_`q'=c512
g syria_`q'=c541
g turkey_`q'=c239
g ethiopia_`q'=c302
g srilanka_`q'=c506
g china_`q'=c508
g iran_`q'=c513

drop c5* c3* c2*

merge 1:1 cantass fyear using "$dat/cant_fperm_`q'.dta", keepusing(c348  c362 c501 c512 c541 c239 c302 c506 c508 c513) 
drop if _merge==2
drop _merge

replace somalia_`q'=somalia_`q'+c348
replace afghanistan_`q'=afghanistan_`q'+c501
replace eritrea_`q'=eritrea_`q'+c362
replace iraq_`q'=iraq_`q'+c512
replace syria_`q'=syria_`q'+c541
replace turkey_`q'=turkey_`q'+c239
replace ethiopia_`q'=ethiopia_`q'+c302
replace srilanka_`q'=srilanka_`q'+c506
replace china_`q'=china_`q'+c508
replace iran_`q'=iran_`q'+c513

drop c5* c3* c2*
}


drop fyear 


cap pr drop c
pr de c
	replace id = `1' if cant=="`2'" 
end
g id=.

*id in the shp file 								 	 		
c 25  AG 
c 6   AR	  
c 12  AI		
c 22  BL	 
c 18  BS		
c 15  BE		 
c 14  FR		 
c 9   GE		
c 26  GL	  
c 7   GR	  
c 19  JU			
c 5   LU			
c 4   NE			
c 17  NW			
c 2   OW		 	
c 3   SG				
c 16  SH		
c 10  SZ		
c 1   SO			
c 20  TG			
c 21  TI			
c 24  UR			
c 23  VS		
c 13  VD					
c 8   ZH					
c 11  ZG		

saveold "$dat/cant_map.dta", replace version(12)





